using System;
using System.Collections.Generic;
using System.Data;
using Mono.Data.SqliteClient;
using Anarchy;

namespace Anarchy.DB
{

    enum QueryType
    {
        SELECT,
        UPDATE,
        INSERT
    }

	public class SQLiteWrapper : DBWrapper
	{
		
		// Variables
			// SQLite
			IDbConnection DB;
			IDbCommand Cmd;
			
		// Connect
		public SQLiteWrapper(String File)
		{
		
			// Connect
			DB = (IDbConnection) new SqliteConnection("URI=file:" + File);
			DB.Open();
		
			// Make command
			Cmd = DB.CreateCommand();	
		
		}
		
		// Deconstruct / Disconnect
		~SQLiteWrapper()
		{
		
			// Disconnect etc.
			Cmd.Dispose();
			DB.Close();
			Cmd = null;
			DB = null;
		
		}
		
		// Query
		private IDataReader Query(String SQL)
		{
		
			// Make Query
			Cmd.CommandText = SQL;
			
			// Do Query and return results
			return Cmd.ExecuteReader();
		
		}
		
		// -- Database Functions --
		
		// Add Image To Database
		public override void Add(ImageInfo Img)
		{
		
			// Form Query
			String SQL = "INSERT INTO `images` (`id`, `path`, `filename`, `filesize`, `checksum`, `tags`) VALUES (" + Img.ID + ", '" + Img.FileName + "', " + Img.FileSize + ", '" + Img.CheckSum + "', '" + Img.Tags + "', '" + Img.Path + "')";
			
			// Execute
			Query(SQL);
		
		}
		
		// Update Image
		public override void Update(ImageInfo Img)
		{
			// To be implemented.
			throw new Exception("Method not implemented.");
		}
		
        // Image exist?
        public override bool Exist(int ID)
        {
            
            // Query
            IDataReader Reader = Query("SELECT * FROM `images` WHERE `id`=" + ID);
			
            // How many results?
            if (Reader.RecordsAffected >= 1)
                return true;
            else
                return false;

        }

		// Delete Image
		public override void Delete(ImageInfo Img)
		{
		
			Delete(Img.ID);
		
		}
		public override void Delete(int ID)
		{
		
			// SQL & Execute
			Query("DELETE FROM `images` WHERE `id`=" + ID);
		
		}
		
		// Select Image[s]
		public override ImageInfo Select(int ID)
		{
			
			// SQL Query
			IDataReader Reader = Query("SELECT * FROM `images` WHERE `id`=" + ID);
			
			// Get the values
			ImageInfo Img = new ImageInfo(Reader.GetInt32(0), Reader.GetString(1), Reader.GetString(2), Reader.GetString(3), Reader.GetInt32(4), Reader.GetString(5));
			
			return Img;
			
		}
        public override ImageInfo Select(ref ImageInfo Img)
        {
            
            // Work out Query
                // 

        }
		public override ImageInfo[] Select(String[] Tags)
		{
			
			// SQL Query
				// The tags part
				String WhereStatement = String.Empty;
				foreach (String Tag in Tags)
					WhereStatement += "`tags` LIKE '%" + Tag + "%' OR ";
				WhereStatement = WhereStatement.Substring(0, WhereStatement.Length - 4); // Get rid of final " OR "
				// Query & Execute
				IDataReader Reader = Query("SELECT * FROM `images` WHERE " + WhereStatement);
			
			// Go through and get records and store in array
			ImageInfo[] Images = new ImageInfo[Reader.RecordsAffected];
			int i = 0;
			while (Reader.Read())
			{
			
				// Add to array
				Images[i] = new ImageInfo(Reader.GetInt32(0), Reader.GetString(1), Reader.GetString(2), Reader.GetString(3), Reader.GetInt32(4), Reader.GetString(5));
			
				i++;
				
			}
			
			// Return
			return Images;
			
		}

        private String ConstructQuery(QueryType Type, ImageInfo Img)
        {

            // Variables
            String Query;
            Dictionary<int, String> NullFields = new Dictionary<int, string>();
            Dictionary<int, String> Fields = new Dictionary<int,string>();

            // What type of query?
            switch (Type)
            {

                // SELECT
                case QueryType.SELECT:

                    // What fields are null?
                        // ID?
                        if (Img.ID == null)
                            NullFields.Add(NullFields.Count, "id");
                        else
                            Fields.Add(Fields.Count, "id");
                        // FileSize?
                        if (Img.FileSize == null)
                            NullFields.Add(NullFields.Count, "filesize");
                        else
                            Fields.Add(Fields.Count, "filesize");
                        // FileName?
                        if (Img.FileName == null)
                            NullFields.Add(NullFields.Count, "filename");
                        else
                            Fields.Add(Fields.Count, "filename");
                        // Path?
                        if (Img.Path == null)
                            NullFields.Add(NullFields.Count, "path");
                        else
                            Fields.Add(Fields.Count, "path");
                        // CheckSum?
                        if (Img.CheckSum == null)
                            NullFields.Add(NullFields.Count, "checksum");
                        else
                            Fields.Add(Fields.Count, "checksum");
                        // Tags?
                        if (Img.Tags == null)
                            NullFields.Add(NullFields.Count, "tags");
                        else
                            Fields.Add(Fields.Count, "tags");

                    // Begining of query
                    Query = "SELECT (";

                    // Work out which fields to select
                    foreach (String Field in NullFields.Values)
                        Query += Field + ", ";
                    Query.Substring(0, Query.Length - 2);

                    // Prepare for the criteria
                    Query = ") WHERE "; // Remove last ", " and add a ") WHERE "

                    // Work out which fields to use as criteria
                    foreach (String Field in Fields.Values)
                        Query += "`" + Field + "` = " + Field;

                    break;

            }

        }

	}
}
